# 创建工具类
import pymysql


class DBTools(object):
    # 建立连接
    # 创建静态方法
    @staticmethod
    def __db_connect():
        conn = pymysql.connect(host="211.103.136.244",
                               port=7061,
                               user="student",
                               password="iHRM_student_2022",
                               database="ihrm",
                               charset="utf8")
        return conn
        # 建立连接，返回连接对象

    # 查询数据库
    @staticmethod
    def db_query_one(sql):
        conn = None
        cursor = None
        try:
            # 创建连接

            conn = DBTools.__db_connect()

            # 创建游标
            cursor = conn.cursor()

            # 执行 sql 语句 --- 查询
            cursor.execute(sql)

            # 执行sql语句，获取结果集
            ret1 = cursor.fetchone()

            # 返回 查询的一条记录
            print("\n--------------------查询1条--------------------\n")
            return ret1

        except Exception as err:
            print("出现异常了,出现的异常是", err)

        finally:
            # 关闭游标
            cursor.close()

            # 关闭连接
            conn.close()

    @staticmethod
    def db_query_many(sql, n):
        conn = None
        cursor = None
        try:
            # 创建连接

            conn = DBTools.__db_connect()

            # 创建游标
            cursor = conn.cursor()

            # 执行 sql 语句 --- 查询
            cursor.execute(sql)

            # 执行sql语句，获取结果集
            ret2 = cursor.fetchmany(n)

            # 返回 查询的一条记录
            print(f"\n--------------------查询{n}条--------------------\n")
            return ret2

        except Exception as err:
            print("出现异常了,出现的异常是", err)

        finally:
            # 关闭游标
            cursor.close()

            # 关闭连接
            conn.close()

    @staticmethod
    def db_query_all(sql):
        conn = None
        cursor = None
        try:
            # 创建连接

            conn = DBTools.__db_connect()

            # 创建游标
            cursor = conn.cursor()

            # 执行 sql 语句 --- 查询
            cursor.execute(sql)

            # 执行sql语句，获取结果集
            ret3 = cursor.fetchall()

            # 返回 查询的一条记录
            print("\n--------------------查询所有--------------------\n")
            return ret3

        except Exception as err:
            print("出现异常了,出现的异常是", err)

        finally:
            # 关闭游标
            cursor.close()

            # 关闭连接
            conn.close()

    # 数据库增删改
    @staticmethod
    def db_insert_delete_update(sql):
        conn = None
        cursor = None
        try:
            # 创建连接

            conn = DBTools.__db_connect()

            # 创建游标
            cursor = conn.cursor()

            # 执行 sql 语句 --- 查询
            cursor.execute(sql)

            # 提交事务
            conn.commit()

            if sql.find("insert") != -1:
                print("\n--------------------添加完成--------------------\n")
            elif sql.find("update") != -1:
                print("\n--------------------修改完成--------------------\n")
            elif sql.find("delete") != -1:
                print("\n--------------------删除完成--------------------\n")

        except Exception as err:

            print("出现异常了,出现的异常是", err)

            # 回滚事务
            conn.rollback()

            print("回滚完成")

        finally:
            # 关闭游标
            cursor.close()

            # 关闭连接
            conn.close()


if __name__ == '__main__':
    ret1 = DBTools.db_query_one("select * from bs_user where mobile = '13451419198'")
    print(ret1)
    # ret2 = DBTools.db_query_many("select * from t_hero", 4)
    # print(ret2)
    # ret3 = DBTools.db_query_all("select * from t_hero")
    # print(ret3)
    # DBTools.db_insert_delete_update("insert into t_book(id, title, pub_date)value(7, '射只因英雄传', '1986-01-01');")
    # DBTools.db_insert_delete_update("update t_book set `read` = `read` + 114514 where title = '射只因英雄传'")
    # # 删除修改反复执行不会报错
    # DBTools.db_insert_delete_update("delete from t_book where title = '射只因英雄传'")
